﻿using System.Data;
using System.Data.SqlClient;
using BLL.Services.Pokemons.Listing;

namespace BLL.Services.Pokemons
{
    public class PokemonsCollection
    {
        private readonly string _connectionString ;

        public PokemonsCollection(string connectionString)
        {
            _connectionString = connectionString;
        }

        public Pokemon GetById(int pokemonId)
        {
            Pokemon result = null;
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Pokemons.GetById(conn, pokemonId);
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        result = GetPokemon(reader);
                    }
                }
            }
            return result;
        }

        public void Create(Pokemon model)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Pokemons.Insert(
                    conn,
                    model.Name,
                    model.Height,
                    model.Weight,
                    (int)model.Element,
                    model.ParentId);
                
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        if (reader.HasRows)
                        {
                            model = GetPokemon(reader);
                        }
                    }
                }
            }
        }

        public void Save(Pokemon model)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Pokemons.Update(
                    conn,
                    model.PokemonId,
                    model.Name,
                    model.Height,
                    model.Weight,
                    (int)model.Element,
                    model.ParentId);

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        if (reader.HasRows)
                        {
                            model = GetPokemon(reader);
                        }
                    }
                }
            }
        }

        public void Delete(int pokemonId)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Pokemons.Delete(conn, pokemonId);
                cmd.ExecuteNonQuery();
            }
        }

        public PokemonListing GetPokemonListing(
          int page, int size, string column, int direction, PokemonFilter filter)
        {
            var result = new PokemonListing()
            {
                Page = page,
                Size = size,
                Column = column,
                Direction = direction,
                Filter = filter
            };
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Pokemons.GetListing(
                    conn, page, size, column,direction, filter.Name, filter.Element);
                var totalCount = new SqlParameter("@TotalCount", SqlDbType.Int, 4)
                {
                    Direction =
                        ParameterDirection
                        .ReturnValue
                };
                cmd.Parameters.Add(totalCount);
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var item = new PokemonListing.Pokemon();
                        item.PokemonId = reader.GetInt32(0);
                        item.Name = reader.GetString(1);
                        item.Element = (Elements)reader.GetInt32(2);
                        result.Pokemons.Add(item);
                    }
                }
                result.TotalPages = (int)totalCount.Value % size == 0
                                       ? (int)totalCount.Value / size
                                       : ((int)totalCount.Value / size) + 1;
            }
            return result;
        }

        private Pokemon GetPokemon(SqlDataReader reader)
        {
            var pokemon = new Pokemon
                              {
                                  PokemonId = reader.GetInt32(0),
                                  Name = reader.GetString(1),
                                  Height = reader.GetInt32(2),
                                  Weight = reader.GetInt32(3),
                                  Element = (Elements)reader.GetInt32(4),
                                  ParentId = reader.GetInt32(5)
                              };
            return pokemon;
        }
    }
}
